package ae.pcfc.etks.online.itworx.ehs.web.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.WordUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.stereotype.Repository;

import ae.pcfc.etks.online.itworx.ehs.web.dao.WorkflowDAO;
import ae.pcfc.etks.online.itworx.ehs.web.entity.Comment;
import ae.pcfc.etks.online.itworx.ehs.web.entity.FNPDetails;
import ae.pcfc.etks.online.itworx.ehs.web.entity.OFCRequest;
import ae.pcfc.etks.online.itworx.ehs.web.entity.OFCRequestHistory;
import ae.pcfc.etks.online.itworx.ehs.web.entity.Request;
import ae.pcfc.etks.online.itworx.ehs.web.enums.RequestField;
import ae.pcfc.etks.online.itworx.ehs.web.enums.RequestStatus;
import ae.pcfc.etks.online.itworx.ehs.web.enums.RequestType;
import ae.pcfc.etks.online.itworx.ehs.web.enums.UserType;
import ae.pcfc.etks.online.itworx.ehs.web.model.RequestSearchCriteria;
import ae.pcfc.etks.online.itworx.ehs.web.util.ServiceValidator;
import ae.pcfc.etks.online.itworx.ehs.web.util.UtilCommon;

@Repository
public class WorkflowDAOImpl extends BaseDAOImpl implements WorkflowDAO {

	private Map<String, String> generalMap;

	@Resource(name = "generalMap")
	public void setGeneralMap(Map<String, String> generalMap) {
		this.generalMap = generalMap;
	}
	
	public long getAllRequestsSize(String userId, String companyName,
			UserType user, Date creationDateFrom, Date creationDateTo,
			RequestStatus requestStatus) {
		Query query = getHibernateTemplate()
				.getSessionFactory()
				.getCurrentSession()
				.createQuery(
						"select count(id) from Request where lower(userId)=:userId");

		query.setParameter("userId", userId.toLowerCase());
		Long size = ((Long) query.iterate().next());
		if (null != size) {
			return size.longValue();
		}
		return 0;
	}

	public List<Request> getAllRequests(String userId, String companyName,
			UserType user, Date creationDateFrom, Date creationDateTo,
			RequestStatus requestStatus, RequestField sortField,
			boolean ascending, int pageNo) {

		if (pageNo < 0) {
			pageNo = 0;
		}

		Criteria criteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(Request.class);

		if (null != user && user.equals(UserType.User)) {
			criteria.add(Restrictions.eq("userId", userId).ignoreCase());
		}

		if (null != user && user.equals(UserType.Officer)) {
			if (null != creationDateFrom && null != creationDateTo) {
				criteria.add(Restrictions.between("creationDate",
						creationDateFrom, creationDateTo));
			}

			if (null != requestStatus) {
				criteria.add(Restrictions.eq("status", requestStatus));
			}
		}
        int pageSize = Integer.parseInt(generalMap.get("pageSize"));
		criteria.setFirstResult(pageNo * pageSize)
				.setMaxResults(pageSize);

		if (null != sortField) {
			if (ascending) {
				criteria.addOrder(Order.asc(sortField.getPropertyName()));
			} else {
				criteria.addOrder(Order.desc(sortField.getPropertyName()));
			}
		} else {
			if (ascending) {
				criteria.addOrder(Order.asc(RequestField.CreationDate
						.getPropertyName()));
			} else {
				criteria.addOrder(Order.desc(RequestField.CreationDate
						.getPropertyName()));
			}
		}
		List<Request> requests = criteria.list();
		return requests;
	}

	public List<OFCRequest> getAllRequestsByStatus(
			List<RequestStatus> requestsStatus) {

		Criteria criteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(Request.class);

		if (null != requestsStatus && !requestsStatus.isEmpty()) {
			criteria.add(Restrictions.in(RequestField.Status.getPropertyName(), requestsStatus));
		}

		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		List<OFCRequest> requests = criteria.list();
		
		if(requests  != null){
			for(int i=0; i<requests.size(); i++){
				List<Comment> comments = requests.get(i).getComments();
				List<FNPDetails> fnpDetails = requests.get(i).getFnpList();
				List<OFCRequestHistory> histories = requests.get(i).getOfcHistory();
				int c = comments.size();
				int h = histories.size();
				int f = fnpDetails.size();
			}
		}
		return requests;
	}

	public OFCRequest getRequestDetails(Long requestId) {

		return getObject(OFCRequest.class, requestId);

	}

	public List<OFCRequestHistory> getRequestHistory(Long requestId) {
		return getObject(OFCRequest.class, requestId).getOfcHistory();
	}

	public void createOFCRequest(OFCRequest ofcRequest) {
		saveObject(ofcRequest);
	}

	public void createOFCRequestHistory(OFCRequestHistory ofcRequestHistory) {
		saveObject(ofcRequestHistory);
	}

	private Criteria buildDbCriteriaFromSearchCriteria(
			RequestSearchCriteria requestsCriteria, Criteria dbCriteria) {
		if (ServiceValidator
				.validateString(requestsCriteria.getCompanyNameAr())) {
			dbCriteria.add(Restrictions.ilike("companyNameArabic",
					"%" + requestsCriteria.getCompanyNameAr() + "%"));
			System.out.println("***************companyNameArabic  == "
					+ requestsCriteria.getCompanyNameAr());
		}

		if (ServiceValidator
				.validateString(requestsCriteria.getCompanyNameEn())) {
			dbCriteria.add(Restrictions.ilike(
					RequestField.CompanyNameEn.getPropertyName(),
					"%" + requestsCriteria.getCompanyNameEn() + "%"));
			System.out.println("***************companyNameEnglish  == "
					+ requestsCriteria.getCompanyNameEn());
		}

		if (null != requestsCriteria.getReceivedDate()) {
			dbCriteria.add(Restrictions.eq(
					RequestField.ProcessingDate.getPropertyName(),
					requestsCriteria.getReceivedDate()));
		}

		if (null != requestsCriteria.getSubmissionDate()) {
			dbCriteria.add(Restrictions.eq(
					RequestField.CreationDate.getPropertyName(),
					requestsCriteria.getSubmissionDate()));
		}

		if (null != requestsCriteria.getViewRequestId()) {
			dbCriteria.add(Restrictions.eq(
					RequestField.CreationDate.getPropertyName(),
					requestsCriteria.getCreationDate()));
			dbCriteria.add(Restrictions.eq(RequestField.Type.getPropertyName(),
					RequestType.valueOf(WordUtils.capitalize(requestsCriteria.getRequestType().toLowerCase()))));
			dbCriteria.add(Restrictions.eq(
					RequestField.RequestId.getPropertyName(),
					requestsCriteria.getRequestId()));
		}

		if (null != RequestStatus
				.getStatus(requestsCriteria.getRequestStatus())) {
			dbCriteria.add(Restrictions.eq(RequestField.Status
					.getPropertyName(), RequestStatus
					.getStatus(requestsCriteria.getRequestStatus())));
		}

		return dbCriteria;
	}

	@Override
	public List<Request> getAllRequests(String userName, boolean isOfficer,
			RequestSearchCriteria searchCriteria) {

		Criteria dbCriteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(Request.class);

		// 1- add restrictions based on dates and company name
		if (null != searchCriteria) {
			dbCriteria = buildDbCriteriaFromSearchCriteria(searchCriteria,
					dbCriteria);
		}
		// 2- set status filter and userId filter based on user role
		// (officer/customer)
		RequestStatus[] statusList = RequestStatus.values();
		if (isOfficer) {
			dbCriteria.add((Restrictions.eq(
					RequestField.OfficerId.getPropertyName(), userName).ignoreCase()));
		} else {
			dbCriteria.add(Restrictions.eq(
					RequestField.UserId.getPropertyName(), userName).ignoreCase());
		}

		if (searchCriteria == null
				|| RequestStatus.getStatus(searchCriteria.getRequestStatus()) == null
				|| "".equals(RequestStatus.getStatus(searchCriteria
						.getRequestStatus()))) {
			dbCriteria.add(Restrictions.in(
					RequestField.Status.getPropertyName(), statusList));
		} else {
			dbCriteria
					.add(Restrictions.eq(RequestField.Status.getPropertyName(),
							RequestStatus.getStatus(searchCriteria
									.getRequestStatus())));
		}
		int pageSize = Integer.parseInt(generalMap.get("pageSize"));
		// 3- add ordering and sorting
		if (null != searchCriteria) {
			if (null != searchCriteria.getSortField()) {
				if (searchCriteria.isAssending()) {
					dbCriteria.addOrder(Order.asc(searchCriteria
							.getSortField()));
					dbCriteria.addOrder(Order.asc(RequestField.RequestId
								.getPropertyName()));
				} else {
					dbCriteria.addOrder(Order.desc(searchCriteria
							.getSortField()));
					dbCriteria.addOrder(Order.desc(RequestField.RequestId
								.getPropertyName()));
				}
			} else {
				if (searchCriteria.isAssending()) {
					dbCriteria.addOrder(Order.asc(RequestField.CreationDate
							.getPropertyName()));
					dbCriteria.addOrder(Order.asc(RequestField.RequestId
							.getPropertyName()));
				} else {
					dbCriteria.addOrder(Order.desc(RequestField.CreationDate
							.getPropertyName()));
					dbCriteria.addOrder(Order.desc(RequestField.RequestId
							.getPropertyName()));
				}
			}
			int firstResultFactor = searchCriteria.getPageNumber() - 1;
			if (firstResultFactor == -1) {
				firstResultFactor = 0;
			}
			dbCriteria.setFirstResult(
					(firstResultFactor) * pageSize)
					.setMaxResults(pageSize);
		} else {
			dbCriteria.addOrder(Order.asc(RequestField.CreationDate
					.getPropertyName()));
			dbCriteria.addOrder(Order.asc(RequestField.RequestId
					.getPropertyName()));
			dbCriteria.setFirstResult(0).setMaxResults(pageSize);
		}
		// ********************* this part to eleminate retrival of un needed
		// columns

		dbCriteria
				.setProjection(Projections
						.projectionList()
						.add(Projections.property(RequestField.ProcessId
								.getPropertyName()),
								RequestField.ProcessId.getPropertyName())
						.add(Projections.property(RequestField.Status
								.getPropertyName()),
								RequestField.Status.getPropertyName())
						.add(Projections.property(RequestField.CreationDate
								.getPropertyName()),
								RequestField.CreationDate.getPropertyName())
						.add(Projections.property(RequestField.ProcessingDate
								.getPropertyName()),
								RequestField.ProcessingDate.getPropertyName())
						.add(Projections.property("companyNameArabic"),
								"companyNameArabic")
						.add(Projections.property(RequestField.CompanyNameEn
								.getPropertyName()),
								RequestField.CompanyNameEn.getPropertyName())
						.add(Projections.property(RequestField.RequestId
								.getPropertyName()),
								RequestField.RequestId.getPropertyName())
						.add(Projections.property(RequestField.Service
								.getPropertyName()),
								RequestField.Service.getPropertyName())
						.add(Projections.property(RequestField.Type
								.getPropertyName()),
								RequestField.Type.getPropertyName()));
		dbCriteria.setResultTransformer(new AliasToBeanResultTransformer(
				Request.class));
		// *****************************************
		return dbCriteria.list();
	}

	@Override
	public List<Request> getAllRequests(String userName, boolean isOfficer,
			RequestSearchCriteria searchCriteria, List<String> tasksUsers,boolean isAssigned) {
		Criteria dbCriteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(Request.class);

		// 1- add restrictions based on dates and company name
		if (null != searchCriteria) {
			dbCriteria = buildDbCriteriaFromSearchCriteria(searchCriteria,
					dbCriteria);
		}
		// 2- set status filter and userId filter based on user role
		// (officer/customer)
		List<RequestStatus> statusList = new ArrayList<RequestStatus>();
		if (isOfficer) {
			statusList.add(RequestStatus.Inspection_Approved);
			statusList.add(RequestStatus.Submitted);
			statusList.add(RequestStatus.Resubmitted);
			statusList.add(RequestStatus.Payment_Done);
			statusList.add(RequestStatus.Payment_Confirmed);
			statusList.add(RequestStatus.Inspection_In_Progress);
			statusList.add(RequestStatus.Comments_Replied);
			statusList.add(RequestStatus.Additional_Payment_Done);
			// requests that are not assigned to any officer yet
			// this step is already done in K2
			/*
			 * dbCriteria .add(Restrictions .disjunction() .add(Restrictions.eq(
			 * RequestField.OfficerId.getPropertyName(), userName))
			 * .add(Restrictions.isNull(RequestField.OfficerId
			 * .getPropertyName()))
			 * .add(Restrictions.isEmpty(RequestField.OfficerId
			 * .getPropertyName())));
			 */
			if(isAssigned){
				tasksUsers.add(userName.toLowerCase());
				dbCriteria.add(UtilCommon.insensitiveIn(RequestField.OfficerId.getPropertyName(), tasksUsers.toArray(new String[tasksUsers.size()])));
			} else {
				dbCriteria.add(Restrictions.isNull(RequestField.OfficerId.getPropertyName()));
			}
		} else {
			// this list should be modified to contain the true status values
			// for a customer
			statusList.add(RequestStatus.Return_For_Update);
			statusList.add(RequestStatus.Approved);
			statusList.add(RequestStatus.Non_Compliance_Comments_Exists);
			statusList.add(RequestStatus.Additional_Payment_Required);
			// this step is already done in K2
			/*
			 * dbCriteria.add(Restrictions.eq(
			 * RequestField.UserId.getPropertyName(), userName));
			 */
			dbCriteria.add(Restrictions.eq(
					RequestField.UserId.getPropertyName(), userName).ignoreCase());
		}

		if (searchCriteria == null
				|| RequestStatus.getStatus(searchCriteria.getRequestStatus()) == null
				|| "".equals(RequestStatus.getStatus(searchCriteria
						.getRequestStatus()))) {
			dbCriteria.add(Restrictions.in(
					RequestField.Status.getPropertyName(), statusList));
		} else {
			dbCriteria
					.add(Restrictions.eq(RequestField.Status.getPropertyName(),
							RequestStatus.getStatus(searchCriteria
									.getRequestStatus())));
		}
		int pageSize = Integer.parseInt(generalMap.get("pageSize"));
		// 3- add ordering and sorting
		if (null != searchCriteria) {
			if (null != searchCriteria.getSortField()) {
				if (searchCriteria.isAssending()) {
					dbCriteria.addOrder(Order.asc(searchCriteria.getSortField()));
					if(searchCriteria.getSortField().equals(RequestField.CreationDate
							.getPropertyName()))
					{
						dbCriteria.addOrder(Order.asc(RequestField.RequestId
								.getPropertyName()));
					}
				} else {
					dbCriteria.addOrder(Order.desc(searchCriteria
							.getSortField()));
					if(searchCriteria.getSortField().equals(RequestField.CreationDate
							.getPropertyName()))
					{
						dbCriteria.addOrder(Order.desc(RequestField.RequestId
								.getPropertyName()));
					}
				}
			} else {
				if (searchCriteria.isAssending()) {
					dbCriteria.addOrder(Order.asc(RequestField.CreationDate
							.getPropertyName()));
					dbCriteria.addOrder(Order.asc(RequestField.RequestId
							.getPropertyName()));
				} else {
					dbCriteria.addOrder(Order.desc(RequestField.CreationDate
							.getPropertyName()));
					dbCriteria.addOrder(Order.desc(RequestField.RequestId
							.getPropertyName()));
				}
			}
			int firstResultFactor = searchCriteria.getPageNumber() - 1;
			if (firstResultFactor == -1) {
				firstResultFactor = 0;
			}
			dbCriteria.setFirstResult(
					(firstResultFactor) *pageSize)
					.setMaxResults(pageSize);
		} else {
			dbCriteria.addOrder(Order.asc(RequestField.CreationDate
					.getPropertyName()));
			dbCriteria.addOrder(Order.asc(RequestField.RequestId
					.getPropertyName()));
			dbCriteria.setFirstResult(0).setMaxResults(pageSize);
		}
		// ********************* this part to eleminate retrival of un needed
		// columns

		dbCriteria
				.setProjection(Projections
						.projectionList()
						.add(Projections.property(RequestField.ProcessId
								.getPropertyName()),
								RequestField.ProcessId.getPropertyName())
						.add(Projections.property(RequestField.Status
								.getPropertyName()),
								RequestField.Status.getPropertyName())
						.add(Projections.property(RequestField.CreationDate
								.getPropertyName()),
								RequestField.CreationDate.getPropertyName())
						.add(Projections.property(RequestField.ProcessingDate
								.getPropertyName()),
								RequestField.ProcessingDate.getPropertyName())
						.add(Projections.property("companyNameArabic"),
								"companyNameArabic")
						.add(Projections.property(RequestField.CompanyNameEn
								.getPropertyName()),
								RequestField.CompanyNameEn.getPropertyName())
						.add(Projections.property(RequestField.RequestId
								.getPropertyName()),
								RequestField.RequestId.getPropertyName())
						.add(Projections.property(RequestField.Service
								.getPropertyName()),
								RequestField.Service.getPropertyName())
						.add(Projections.property(RequestField.Type
								.getPropertyName()),
								RequestField.Type.getPropertyName()));
		dbCriteria.setResultTransformer(new AliasToBeanResultTransformer(
				Request.class));
		// *****************************************

		return dbCriteria.list();
	}

	public long getAllRequestsSize(String userName, boolean isOfficer,
			RequestSearchCriteria searchCriteria) {

		Criteria dbCriteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(Request.class);

		// 1- add restrictions based on dates and company name
		if (null != searchCriteria) {
			dbCriteria = buildDbCriteriaFromSearchCriteria(searchCriteria,
					dbCriteria);
		}
		// 2- set status filter and userId filter based on user role
		// (officer/customer)
		RequestStatus[] statusList = RequestStatus.values();
		if (isOfficer) {
			// requests that are only assigned to this officer
			dbCriteria.add((Restrictions.eq(
					RequestField.OfficerId.getPropertyName(), userName).ignoreCase()));
		} else {
			dbCriteria.add(Restrictions.eq(
					RequestField.UserId.getPropertyName(), userName).ignoreCase());
		}

		if (searchCriteria == null
				|| RequestStatus.getStatus(searchCriteria.getRequestStatus()) == null
				|| "".equals(RequestStatus.getStatus(searchCriteria
						.getRequestStatus()))) {
			dbCriteria.add(Restrictions.in(
					RequestField.Status.getPropertyName(), statusList));
		} else {
			dbCriteria
					.add(Restrictions.eq(RequestField.Status.getPropertyName(),
							RequestStatus.getStatus(searchCriteria
									.getRequestStatus())));
		}

		// 3- get row count
		Long total = (Long) dbCriteria.setProjection(Projections.rowCount())
				.uniqueResult();
		return total;
	}

	@Override
	public long getAllActiveTasksSize(String userName, boolean isOfficer,
			RequestSearchCriteria searchCriteria, List<String> tasksUsers,boolean isAssigned) {
		Criteria dbCriteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(Request.class);

		// 1- add restrictions based on dates and company name
		if (null != searchCriteria) {
			dbCriteria = buildDbCriteriaFromSearchCriteria(searchCriteria,
					dbCriteria);
		}
		// 2- set status filter and userId filter based on user role
		// (officer/customer)
		List<RequestStatus> statusList = new ArrayList<RequestStatus>();
		if (isOfficer) {
			statusList.add(RequestStatus.Inspection_Approved);
			statusList.add(RequestStatus.Submitted);
			statusList.add(RequestStatus.Resubmitted);
			statusList.add(RequestStatus.Payment_Done);
			statusList.add(RequestStatus.Payment_Confirmed);
			statusList.add(RequestStatus.Inspection_In_Progress);
			statusList.add(RequestStatus.Comments_Replied);
			statusList.add(RequestStatus.Additional_Payment_Done);
			// requests that are not assigned to any officer yet
			// this step is already done in K2
			/*
			 * dbCriteria.add( Restrictions.disjunction() .add(
			 * Restrictions.eq(RequestField.OfficerId.getPropertyName(),
			 * userName)) .add(
			 * Restrictions.isNull(RequestField.OfficerId.getPropertyName()))
			 * .add(
			 * Restrictions.isEmpty(RequestField.OfficerId.getPropertyName()))
			 * );
			 */
			if(isAssigned){
				tasksUsers.add(userName.toLowerCase());
				dbCriteria.add(UtilCommon.insensitiveIn(RequestField.OfficerId.getPropertyName(), tasksUsers.toArray(new String[tasksUsers.size()])));
			} else {
				dbCriteria.add(Restrictions.isNull(
						RequestField.OfficerId.getPropertyName()));
			}
		} else {
			statusList.add(RequestStatus.Return_For_Update);
			statusList.add(RequestStatus.Approved);
			statusList.add(RequestStatus.Non_Compliance_Comments_Exists);
			statusList.add(RequestStatus.Additional_Payment_Required);
			// this step is already done in K2
			/*
			 * dbCriteria.add(Restrictions.eq(
			 * RequestField.UserId.getPropertyName(), userName));
			 */
			dbCriteria.add(Restrictions.eq(RequestField.UserId.getPropertyName(), userName).ignoreCase());
		}

		if (searchCriteria == null
				|| RequestStatus.getStatus(searchCriteria.getRequestStatus()) == null
				|| "".equals(RequestStatus.getStatus(searchCriteria
						.getRequestStatus()))) {
			dbCriteria.add(Restrictions.in(
					RequestField.Status.getPropertyName(), statusList));
		} else {
			dbCriteria
					.add(Restrictions.eq(RequestField.Status.getPropertyName(),
							RequestStatus.getStatus(searchCriteria
									.getRequestStatus())));
		}

		// 3- get row count
		Long total = (Long) dbCriteria.setProjection(Projections.rowCount())
				.uniqueResult();
		return total;
	}

	@Override
	public OFCRequest getRequestByIdAndUsername(long requestId, String username, List<String> tasksUsers, boolean isOfficer) {
		Criteria criteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(Request.class);

		if(isOfficer){
			tasksUsers.add(username.toLowerCase());
			criteria.add(Restrictions.or(UtilCommon.insensitiveIn(RequestField.OfficerId.getPropertyName(), tasksUsers.toArray(new String[tasksUsers.size()])), Restrictions.isNull(RequestField.OfficerId.getPropertyName())));
		}else{
			criteria.add(Restrictions.eq(RequestField.UserId.getPropertyName(), username).ignoreCase());
		}
		criteria.add(Restrictions.eq(RequestField.RequestId.getPropertyName(), requestId));
		criteria.uniqueResult();
		return (OFCRequest)criteria.uniqueResult();
	}

	@Override
	public void updateOFCRequest(OFCRequest ofcRequest) {
		//getHibernateTemplate().saveOrUpdate(ofcRequest);
		getHibernateTemplate().merge(ofcRequest);
		
	}
	
	@Override
	public long getActiveRequestSize(String userName) {
		Criteria dbCriteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(Request.class);

		List<RequestStatus> statusList = new ArrayList<RequestStatus>();
			statusList.add(RequestStatus.Closed);
			statusList.add(RequestStatus.Rejected);
			statusList.add(RequestStatus.Canceled);
			
			dbCriteria.add(Restrictions.eq(RequestField.TrakheesId.getPropertyName(), userName).ignoreCase());
			
			dbCriteria.add(Restrictions.not(Restrictions.in(RequestField.Status.getPropertyName(), statusList)));
			
		return (Long) dbCriteria.setProjection(Projections.rowCount()).uniqueResult();
	}
}
